﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using DTOFrameworks;
using System.Data;
using FooFun.DataElements.Dtos;
using FooFun.Faq.Dal;
using System.Data.Common;

namespace PhysicalDataProvider
{
    public class TopicIndexService : ITopicService, IReplyService
    {
        DEDataProvider dataProvider;

        public DEDataProvider DataProvider
        {
            get { return dataProvider; }
            set { dataProvider = value; }
        }

        public IDataElement ListTopicOfForum(Guid forumId, int pageSize, int pageIndex)
        {
            string sql = @"SELECT TopicId 
FROM t_TopicIndex
WHERE ForumId='{0}'
ORDER BY TopicNo DESC";

            sql = string.Format(sql,
                DataProviderUtil.GetGuidBase64(forumId));

            DataTable table = DataService.FillTable(sql);

            IDataElement tableDE = new DataTableDataElement(table);

            return tableDE;
        }

        public IDataElement ListAnswerOfUser(Guid accountId, int pageSize, int pageIndex)
        {
            string sql = @"SELECT TopicId 
FROM t_ReplyIndex
WHERE t_ReplyIndex.ReplyAccountId='{0}'
GROUP BY TopicId
ORDER BY Max(EditTime) DESC";

            sql = string.Format(sql,
                DataProviderUtil.GetGuidBase64(accountId));

            DataTable table = DataService.FillTable(sql);

            IDataElement tableDE = new DataTableDataElement(table);

            return tableDE;
        }

        public IDataElement ListReplyOfForum(Guid forumId, int pageSize, int pageIndex)
        {
            string sql = @"SELECT TopicId FROM t_ReplyIndex
WHERE ForumId = '{0}'
GROUP BY TopicId
ORDER BY Max(EditTime) DESC";
            sql = string.Format(sql, DataProviderUtil.GetGuidBase64(forumId));

            DataTable table = DataService.FillTable(sql);

            IDataElement tableDE = new DataTableDataElement(table);

            return tableDE;
        }

        // 用户提问的问题
        public IDataElement ListTopicOfUser(Guid accountId, int pageSize, int pageIndex)
        {
            string sql = @"SELECT TopicId
FROM t_TopicIndex
WHERE UserId='{0}'
ORDER BY TopicId DESC";

            sql = string.Format(sql, DataProviderUtil.GetGuidBase64(accountId));

            DataTable table = DataService.FillTable(sql);

            IDataElement tableDE = new DataTableDataElement(table);

            return tableDE;
        }

        // 最新的提问
        public IDataElement ListLastTopic(int pageSize, int pageIndex)
        {
            string sql;
            if (pageIndex <= 0)
            {
                sql = @"SELECT TOP {0} TopicId
FROM t_TopicIndex
ORDER BY TopicNo DESC";

                sql = string.Format(sql, pageSize, (int)FAQActionEnum.AddTopic);
            }
            else
            {
                sql = @"SELECT TOP {0} TopicId
FROM t_TopicIndex
WHERE TopicId NOT IN 
(SELECT TOP {2} TopicId 
FROM t_TopicIndex
WHERE ORDER BY TopicNo DESC) ORDER BY TopicNo DESC ";

                sql = string.Format(sql, pageSize,
                    (int)FAQActionEnum.AddTopic, pageSize * pageIndex);
            }

            DataTable table = DataService.FillTable(sql);

            IDataElement tableDE = new DataTableDataElement(table);

            return tableDE;
        }

        // 最新的回答
        public IDataElement ListLastReply(int pageSize, int pageIndex)
        {
            string sql = @"SELECT TOP {0} TopicId
FROM t_ReplyIndex
GROUP BY TopicId
ORDER BY Max(EditTime) DESC";

            sql = string.Format(sql, pageSize);

            DataTable table = DataService.FillTable(sql);

            IDataElement tableDE = new DataTableDataElement(table);

            return tableDE;
        }

        // 已经结束的问题
        public IDataElement ListTopicHasAnswer
            (Guid forumId, int pageIndex, int pageSize)
        {
            int i = (int)TopicStatus.Sealed;

            string sql;
            if (pageIndex == 0)
            {
                sql = @"SELECT TOP {0} TopicId FROM t_TopicIndex
WHERE ForumId = '{1}' AND Status = {2} ORDER BY TopicNo DESC";
                sql = string.Format(sql, pageSize,
                    DataProviderUtil.GetGuidBase64(forumId), i);
            }
            else
            {
                sql = @"SELECT TOP {0} TopicId FROM t_TopicIndex
WHERE TopicId NOT IN(SELECT TOP {1} TopicId FROM t_TopicIndex 
WHERE ForumId = '{2}' AND Status = {3} ORDER BY TopicNo DESC)
AND ForumId = '{2}' AND Status = {3} ORDER BY TopicNo DESC";

                sql = string.Format
                    (sql, pageSize, pageIndex * pageSize,
                    DataProviderUtil.GetGuidBase64(forumId), i);
            }
            DataTable table = DataService.FillTable(sql);

            return new DataTableDataElement(table);
        }

        // 未结束的问题
        public IDataElement ListTopicNoAnswer
            (Guid forumId, int pageIndex, int pageSize)
        {
            int status = (int)TopicStatus.Sealed;

            string sql;
            if (pageIndex == 0)
            {
                sql = @"SELECT TOP {0} TopicId FROM t_TopicIndex
WHERE ForumId = '{1}' AND Status <> {2} ORDER BY TopicNo DESC";
                sql = string.Format(sql, pageSize,
                    DataProviderUtil.GetGuidBase64(forumId), status);
            }
            else
            {
                sql = @"SELECT TOP {0} TopicId FROM t_TopicIndex
WHERE TopicId NOT IN(SELECT TOP {1} TopicId FROM t_TopicIndex 
WHERE ForumId = '{2}' AND Status <> {3} ORDER BY TopicNo DESC)
AND ForumId = '{2}' AND Status <> {3} ORDER BY TopicNo DESC";

                sql = string.Format
                    (sql, pageSize, pageIndex * pageSize,
                    DataProviderUtil.GetGuidBase64(forumId), status);
            }
            DataTable table = DataService.FillTable(sql);

            return new DataTableDataElement(table);
        }

        #region ITopicService 成员

        public void CreateTopic(string topicTitle, string remark, Guid eventId, Guid forumId, Guid userId)
        {
            DbParameter para1 = DataProvider.CreateParameter();
            para1.Value = DataProviderUtil.GetGuidBase64(eventId);

            DbParameter para2 = DataProvider.CreateParameter();
            para2.Value = DataProviderUtil.GetGuidBase64(eventId);

            DbParameter para3 = DataProvider.CreateParameter();
            para3.Value = (int)TopicStatus.Asked;

            DbParameter para4 = DataProvider.CreateParameter();
            para4.Value = DataProviderUtil.GetGuidBase64(forumId);

            DbParameter para5 = DataProvider.CreateParameter();
            para5.Value = DataProviderUtil.GetGuidBase64(userId);

            DataProvider.ExecuteNonQuery(@"INSERT INTO t_TopicIndex
(TopicId,EventId,Status,ForumId,UserId)
VALUES
(?,?,?,?,?)", para1, para2, para3, para4, para5);
        }

        public void MoveTopic(IDataElement topic, Guid forumId)
        {
            DbParameter para1 = DataProvider.CreateParameter();
            para1.Value = DataProviderUtil.GetGuidBase64
                (topic["@TopicId"].Value);

            DbParameter para4 = DataProvider.CreateParameter();
            para4.Value = DataProviderUtil.GetGuidBase64(forumId);

            DataProvider.ExecuteNonQuery(@"UPDATE t_TopicIndex
SET ForumId=? WHERE TopicId = ?", para4, para1);
        }

        public void RemoveTopic(Guid topicId)
        {
            DbParameter para1 = DataProvider.CreateParameter();
            para1.Value = DataProviderUtil.GetGuidBase64(topicId);

            DataProvider.ExecuteNonQuery(@"DELETE FROM t_TopicIndex 
WHERE TopicId = ?", para1);
        }

        #endregion

        #region IReplyService 成员

        public void AddReply(IDataElement topic, Guid eventId, Guid userId, string reply)
        {
            DbParameter para1 = DataProvider.CreateParameter();
            para1.Value = DataProviderUtil.GetGuidBase64
                (topic["@TopicId"].Value);

            DbParameter para4 = DataProvider.CreateParameter();
            para4.Value = (int)TopicStatus.Relyed;

            DataProvider.ExecuteNonQuery(@"UPDATE t_TopicIndex
SET [Status]=? WHERE TopicId = ?", para4, para1);
        }

        public void Conclude(IDataElement topic, Guid eventId, Guid replyId, string conclusion)
        {
            DbParameter para1 = DataProvider.CreateParameter();
            para1.Value = DataProviderUtil.GetGuidBase64
                (topic["@TopicId"].Value);

            DbParameter para4 = DataProvider.CreateParameter();
            para4.Value = (int)TopicStatus.Sealed;

            DataProvider.ExecuteNonQuery(@"UPDATE t_TopicIndex
SET [Status]=? WHERE TopicId = ?", para4, para1);
        }

        #endregion

        public int CountAllTopic()
        {
            string sql = @"SELECT Count(*)
FROM t_TopicIndex";

            DataTable table = DataService.FillTable(sql);

            int count = (int)table.Rows[0][0];

            return count;
        }

        public int CountSealedTopic()
        {
            string sql = @"SELECT Count(*)
FROM t_TopicIndex
WHERE Status = {0}";

            sql = string.Format(sql, (int)TopicStatus.Sealed);

            DataTable table = DataService.FillTable(sql);

            int count = (int)table.Rows[0][0];

            return count;
        }
    }
}

